﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class XuLyPhieuThue
    {
        public static DataTable danhSachPhieuThue()
        {
            //List<CPHIEUTHUE> lst = new List<CPHIEUTHUE>();                        
            DAO.CDataProvider reader = new DAO.CDataProvider();
            DataTable dt = new DataTable();
            // chuoi SELECT
            String sql = " SELECT * FROM PHIEUTHUE where TinhTrang=0";            

            try
            {
                // Bang data se duoc nhap
                dt = reader.DocDuLieu(sql);                
                {
                    //foreach (DataRow dr in dt.Rows)
                    //{
                    //    CPHIEUTHUE pt=new CPHIEUTHUE();
                    //    pt.MaPhieu = int.Parse(dr["MaPhieu"].ToString());
                    //    pt.MaKH = int.Parse(dr["MaKH"].ToString());
                    //    pt.MaPhong = dr["MaPhong"].ToString();
                    //    pt.BatDauThue = DateTime.Parse(dr["BatDauThue"].ToString());
                    //    pt.TinhTrang = int.Parse(dr["TinhTrang"].ToString());
                    //    //lst.Add(pt);
                    //}
                }               
            }
            catch (System.Exception)
            {

            }            
            return dt;
        }
        // tim 1 phieu thue theo ma phieu
        public static DTO.CPHIEUTHUE TimPhieuThueTheoMaPhieu(int MaPh)
        {
            DTO.CPHIEUTHUE pt = new DTO.CPHIEUTHUE();
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM PHIEUTHUE " +
            " WHERE MaPhieu = " + MaPh.ToString() + " ";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai nhan vien nay
                if (dt.Rows.Count == 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        pt.MaPhieu = int.Parse(dr["MaPhieu"].ToString());
                        pt.MaKH = int.Parse(dr["MaKH"].ToString());
                        pt.MaPhong = dr["MaPhong"].ToString();
                        pt.BatDauThue = DateTime.Parse(dr["BatDauThue"].ToString());
                        pt.TinhTrang = int.Parse(dr["TinhTrang"].ToString());
                    }
                }
                // nhan vien nay ko ton tai
                else
                {
                    pt.MaPhieu = 0;
                    pt.MaKH = 0;
                    pt.MaPhong = " ";
                    pt.BatDauThue = DateTime.Now;
                    pt.TinhTrang = 0;
                }
            }
            catch (System.Exception )
            {
            	
            }
            

            return pt;
        }
   
        // tim ds phieu thue theo ma KH
        public static List<DTO.CPHIEUTHUE> TimPhieuThueTheoMaKH(int MaKH)
        {
            List<DTO.CPHIEUTHUE> lpt = new List<DTO.CPHIEUTHUE>();
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM PHIEUTHUE " +
            " WHERE MaKH = " + MaKH.ToString() + " ";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai 
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        DTO.CPHIEUTHUE pt = new DTO.CPHIEUTHUE();
                        pt.MaPhieu = int.Parse(dr["MaPhieu"].ToString());
                        pt.MaKH = int.Parse(dr["MaKH"].ToString());
                        pt.MaPhong = dr["MaPhong"].ToString();
                        pt.BatDauThue = DateTime.Parse(dr["BatDauThue"].ToString());
                        pt.TinhTrang = int.Parse(dr["TinhTrang"].ToString());

                        lpt.Add(pt);
                    }
                }
            }
            catch (System.Exception)
            {

            }


            return lpt;
        }
        // tìm 1 phiếu theo mã khách hàng
        public static DTO.CPHIEUTHUE TimMotPhieuThueTheoMaKH(int MaKH)
        {
            DTO.CPHIEUTHUE lpt = new DTO.CPHIEUTHUE();
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM PHIEUTHUE " +
            " WHERE MaKH = " + MaKH.ToString() + " ";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai 
                if (dt.Rows.Count == 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {                        
                        lpt.MaPhieu = int.Parse(dr["MaPhieu"].ToString());
                        lpt.MaKH = int.Parse(dr["MaKH"].ToString());
                        lpt.MaPhong = dr["MaPhong"].ToString();
                        lpt.BatDauThue = DateTime.Parse(dr["BatDauThue"].ToString());
                        lpt.TinhTrang = int.Parse(dr["TinhTrang"].ToString());
                        
                       
                    }
                }
            }
            catch (System.Exception)
            {

            }


            return lpt;
        }
        // them 1 phieu thue
        public static Boolean ThemPhieuThue(DTO.CPHIEUTHUE pt)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String insertSQL = " INSERT INTO PHIEUTHUE(MaKH,MaPhong,BatDauThue,TinhTrang) "
                + " VALUES(" + pt.MaKH.ToString() + ","
                + "'" + pt.MaPhong.ToString() + "',"
                + "'" + pt.BatDauThue.ToShortDateString() + "',"
                + pt.TinhTrang.ToString() + ") ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(insertSQL))
                return true;
            else
                return false;
        }
        // sua tinh trang theo ma KH
        public static Boolean SuaTTPhieuThueTheoMaKH(int TT,int MaKH)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String updateSQL = " UPDATE PHIEUTHUE "
                + " SET TinhTrang = " + TT + " "
                + " WHERE MaKH = " + MaKH + " ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(updateSQL))
                return true;
            else
                return false;
        }
        // sua thong tin 1 phieu thue
        public static Boolean SuaTTPhieuThue(DTO.CPHIEUTHUE pt, int MaPh)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String updateSQL = " UPDATE PHIEUTHUE "
                + " SET MaPhieu = " + pt.MaPhieu.ToString() + ", "
                + " MaKH =" + pt.MaKH.ToString() + ", "
                + " TinhTrang = " + pt.TinhTrang + ","
                + " MaPhong = '" + pt.MaPhong.ToString() + "',"
                + " BatDauThue = '" + pt.BatDauThue.ToShortDateString() + "' "
                + " WHERE MaPhieu =" + MaPh + " ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(updateSQL))
                return true;
            else
                return false;
        }
        // xoa 1 phieu thue
        public static Boolean XoaPhieuThue(int MaPh)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String deleteSQL = " DELETE FROM PHIEUTHUE "
                + " WHERE MaPhieu =" + MaPh + " ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(deleteSQL))
                return true;
            else
                return false;
        }
    }
}
